using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Mvc;
using MvcExtExample.Models;
using Newtonsoft.Json.Linq;

namespace MvcExtExample.Controllers
{
    public class LogManageController : Controller
    {
        //
        // GET: /LogManage/loginLog

        public ActionResult loginLog()
        {
            return View();
        }

        //
        // GET: /LogManage/LogManage

        public ActionResult LogManage()
        {
            return View();
        }

        //
        // GET: /LogManage/ErrorLogManage

        public ActionResult ErrorLogManage()
        {
            return View();
        }
        //
        // GET: /LogManage/LogExtManage

        [AcceptVerbs(HttpVerbs.Get)]
        public string LogOperationQuery()
        {
            int start = 0;
            int TotalRecords;
            int.TryParse(Request.Params["start"], out start);
            StringBuilder strSqlStr = new StringBuilder();
            //strSqlStr.Append("SELECT TOP 15 * FROM [Tb_LogOperation] WHERE (ID NOT IN ( SELECT TOP " + start + " ID FROM [Tb_LogOperation]   ORDER BY ID )) AND [OperationType]!='登录' ");
            string total = "SELECT COUNT(*) FROM [dbo].[Tb_LogOperation] WHERE [OperationType] != '登录'";
            string sql1;
            if (start > 0)
            {
                sql1 = "SELECT TOP 15 * FROM( SELECT * FROM [dbo].[Tb_LogOperation] WHERE [OperationType] != '登录') AS a WHERE a.[ID]>(SELECT MAX([ID]) FROM [dbo].[Tb_LogOperation] WHERE [ID] IN (SELECT TOP " + start + " [ID] FROM [dbo].[Tb_LogOperation] WHERE [OperationType] != '登录')) ";
            }
            else
            {
                sql1 = "SELECT TOP 15 *FROM ( SELECT * FROM [dbo].[Tb_LogOperation] WHERE [OperationType] != '登录' ) AS a where 1=1";
            }
            if (Request.Params["filter"] != null)
            {
                string filter = "", op = "", BusinessesName = "";
                JArray jaf = JArray.Parse(Request.Params["filter"]);
                foreach (JObject jo in jaf)
                {
                    if ((string)jo["property"] == "op")
                        op = (string)jo["value"];
                    if ((string)jo["property"] == "BusinessesName")
                    {
                        BusinessesName = (string)jo["value"];
                        sql1 = sql1.Replace("OperationType", "BusinessesName");
                        sql1 = sql1.Replace("!=", "=");
                        sql1 = sql1.Replace("登录", BusinessesName);
                        total = total.Replace("OperationType", "BusinessesName");
                        total = total.Replace("!=", "=");
                        total = total.Replace("登录", BusinessesName);
                    }
                }
                strSqlStr.Append(sql1);
                sql1 = String.Empty;

                if (op != null)
                {
                    if (op.Length > 0)
                    {
                        DateTime startDate = new DateTime();
                        DateTime endDate = new DateTime();
                        foreach (JObject jo in jaf)
                        {
                            if ((string)jo["property"] == "startDate")
                            {
                                if (!String.IsNullOrEmpty(jo["value"].ToString()))
                                    startDate = Convert.ToDateTime(jo["value"].ToString());
                            }
                            if ((string)jo["property"] == "endDate")
                            {
                                if (!String.IsNullOrEmpty(jo["value"].ToString()))
                                    endDate = Convert.ToDateTime(jo["value"].ToString());
                            }
                        }
                        if (op == "=")
                        {
                            filter += " and  CreateTime ='" + startDate.ToString("yyyy-MM-dd HH:mm:ss") + "' ";
                        }
                        else
                        {
                            filter += String.Format(" and  CreateTime between '{0}' and '{1}' ", startDate.ToString("yyyy-MM-dd HH:mm:ss"), endDate.ToString("yyyy-MM-dd HH:mm:ss"));
                        }
                    }
                }

                foreach (JObject jo in jaf)
                {
                    if ((string)jo["property"] != "startDate" && (string)jo["property"] != "endDate" && (string)jo["property"] != "op")
                    {
                        filter += " AND " + (string)jo["property"] + " LIKE '%" + (string)jo["value"] + "%' ";
                    }
                }
                filter = filter.Substring(0, filter.Length - 1);
                strSqlStr.Append(filter.ToString());
            }
            strSqlStr.Append(sql1);
            framework.SqlHelperCS.SqlHelper cs = new framework.SqlHelperCS.SqlHelper();
            TotalRecords = cs.ReturnSQL(total);
            DataSet DSet = cs.GetDataSet(strSqlStr.ToString());
            if (DSet != null)
            {
                DataTable dt = DSet.Tables[0];
                StringBuilder JsonString = new StringBuilder();
                if (dt != null && dt.Rows.Count > 0)
                {
                    JsonString.Append("{\"total\":" + TotalRecords + ",\"data\":[ ");
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        JsonString.Append("{ ");
                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            if (j < dt.Columns.Count - 1)
                            {
                                JsonString.Append("\"" + dt.Columns[j].ColumnName.ToString() + "\"" + ":" + "\"" + dt.Rows[i][j].ToString() + "\",");
                            }
                            else if (j == dt.Columns.Count - 1)
                            {
                                JsonString.Append("\"" + dt.Columns[j].ColumnName.ToString() + "\"" + ":" + "\"" + dt.Rows[i][j].ToString() + "\"");
                            }
                        }

                        if (i == dt.Rows.Count - 1)
                        {
                            JsonString.Append("} ");
                        }
                        else
                        {
                            JsonString.Append("}, ");
                        }
                    }
                    JsonString.Append("]}");
                    return JsonString.ToString();
                }
                else
                {
                    return null;
                }
            }
            else
            {
                return null;
            }

            //List<LogOperation> litestModel = new List<LogOperation>();
            //framework.JSONHelper jsonHelp = new framework.JSONHelper();
            //int TotalRecords = 0;
            ////string strSqlStr = "SELECT  TOP 20 [OperationType],[TableName],[BusinessesName],[Content],[User_ID],[CreateTime],[IP],[Browser] FROM [dbo].[Tb_LogOperation] WHERE [OperationType]!='登录'";
            //framework.SqlHelperCS.SqlHelper cs = new framework.SqlHelperCS.SqlHelper();
            //DataSet DSet = cs.GetDataSet(strSqlStr);
            //if (DSet != null)
            //{
            //    DataTable DTable = DSet.Tables[0];
            //    for (int i = 0; i < DTable.Rows.Count; i++)
            //    {
            //        DataRowView rowview = DSet.Tables[0].DefaultView[i];
            //        litestModel.Add(new LogOperation()
            //        {
            //            //active = true,//默认为勾选状态
            //            ID = Convert.ToInt32(rowview["ID"]),
            //            OperationType = rowview["OperationType"].ToString(),
            //            TableName = rowview["TableName"].ToString(),
            //            BusinessesName = rowview["BusinessesName"].ToString(),
            //            Content = rowview["Content"].ToString(),
            //            User_ID = rowview["User_ID"].ToString(),
            //            CreateTime = Convert.ToDateTime(rowview["CreateTime"].ToString()),
            //            IP = rowview["IP"].ToString(),
            //            Location = "湖北孝感",
            //            Browser = rowview["Browser"].ToString()
            //        });
            //    }
            //    return Json(litestModel, JsonRequestBehavior.AllowGet);
            //}
            //else
            //{
            //    return null;
            //}
        }

        [AcceptVerbs(HttpVerbs.Get)]
        public string LogHistoryQuery()
        {
            //int start = 0;
            //int limit = 20;
            //int.TryParse(Request.Params["start"], out start);
            //string sort = "it.CreateTime";
            //string filter = "true";
            //string strSqlStr = "SELECT TOP 25 * FROM [Tb_LogOperation] WHERE ( ID NOT IN ( SELECT TOP " + start + " id FROM [Tb_LogOperation] ORDER BY id ) ) AND [OperationType]='登录' ORDER BY ID";


            //List<LogOperation> litestModel = new List<LogOperation>();
            //framework.JSONHelper jsonHelp = new framework.JSONHelper();
            //int TotalRecords = 0;
            ////string strSqlStr = "SELECT  TOP 25 [ID],[OperationType],[TableName],[BusinessesName],[Content],[User_ID],[CreateTime],[IP],[Browser] FROM [dbo].[Tb_LogOperation] WHERE [OperationType]='登录'";
            //framework.SqlHelperCS.SqlHelper cs = new framework.SqlHelperCS.SqlHelper();
            //DataSet DSet = cs.GetDataSet(strSqlStr);
            //if (DSet != null)
            //{
            //    DataTable DTable = DSet.Tables[0];
            //    for (int i = 0; i < DTable.Rows.Count; i++)
            //    {
            //        DataRowView rowview = DSet.Tables[0].DefaultView[i];
            //        litestModel.Add(new LogOperation()
            //        {
            //            active = true,//默认为勾选状态
            //            ID = Convert.ToInt32(rowview["ID"].ToString()),
            //            OperationType = rowview["OperationType"].ToString(),
            //            TableName = rowview["TableName"].ToString(),
            //            BusinessesName = rowview["BusinessesName"].ToString(),
            //            Content = rowview["Content"].ToString(),
            //            User_ID = rowview["User_ID"].ToString(),
            //            CreateTime = Convert.ToDateTime(rowview["CreateTime"].ToString()),
            //            IP = rowview["IP"].ToString(),
            //            Location = "湖北武汉",
            //            Browser = rowview["Browser"].ToString()
            //        });
            //    }
            //    return Json(litestModel, JsonRequestBehavior.AllowGet);
            //}
            //else
            //{
            //    return null;
            //}

            int start = 0;
            int.TryParse(Request.Params["start"], out start);
            framework.JSONHelper jsonHelp = new framework.JSONHelper();
            int TotalRecords = 0;
            string total = "SELECT COUNT(*) FROM [dbo].[Tb_LogOperation] WHERE [OperationType]='登录'";
            string strSqlStr = "SELECT TOP 18 * FROM [Tb_LogOperation] WHERE ID NOT IN ( SELECT TOP " + start + " ID FROM [Tb_LogOperation] WHERE [OperationType]='登录' ORDER BY ID ) AND  [OperationType]='登录' ORDER BY ID";
            framework.SqlHelperCS.SqlHelper cs = new framework.SqlHelperCS.SqlHelper();
            TotalRecords = cs.ReturnSQL(total);
            DataSet DSet = cs.GetDataSet(strSqlStr);
            if (DSet != null)
            {
                DataTable dt = DSet.Tables[0];
                StringBuilder JsonString = new StringBuilder();
                if (dt != null && dt.Rows.Count > 0)
                {
                    JsonString.Append("{\"total\":" + TotalRecords + ",\"data\":[ ");
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        JsonString.Append("{ ");
                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            if (j < dt.Columns.Count - 1)
                            {
                                JsonString.Append("\"" + dt.Columns[j].ColumnName.ToString() + "\"" + ":" + "\"" + dt.Rows[i][j].ToString() + "\",");
                            }
                            else if (j == dt.Columns.Count - 1)
                            {
                                JsonString.Append("\"" + dt.Columns[j].ColumnName.ToString() + "\"" + ":" + "\"" + dt.Rows[i][j].ToString() + "\"");
                            }
                        }

                        if (i == dt.Rows.Count - 1)
                        {
                            JsonString.Append("} ");
                        }
                        else
                        {
                            JsonString.Append("}, ");
                        }
                    }
                    JsonString.Append("]}");
                    return JsonString.ToString();
                }
                else
                {
                    return null;
                }
            }
            else
            {
                return null;
            }
        }

    }
}
